package com.hk.service.impl;

import com.hk.commons.RedisCilent;
import com.hk.commons.entity.Organization;
import com.hk.commons.vo.StatisticsMsg;
import com.hk.service.IOrganization_New;
import com.hk.service.sys.ICacheService;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.math.BigDecimal;
import java.util.List;
import java.util.Map;

@Service
public  class StatisticsHelp {

	private  Logger logger = LoggerFactory.getLogger(StatisticsHelp.class);

	@Autowired
	private  IOrganization_New orgNewService;

	@Resource
	private  JdbcTemplate jdbcTemplate;

	@Autowired
	private ICacheService cacheService;

	/**
	 * 理财师APP使用数
	 */
	private static final String FINANCIAL_APP_USER_COUNT = "financial_app_user_count_";

	public Integer getFinancialAppUserCountByOrgCode(String orgCode, Long begTime, Long endTime) {
		String ids = RedisCilent.getString(FINANCIAL_APP_USER_COUNT+begTime+endTime);
		if(ids == null){
			String sql = "select distinct(userId) as userId from t_financial_log where channel = 2";

			if (begTime != null) {
				sql += " and created >= " + begTime ;
			}

			if (endTime != null) {
				sql += " and  created <= " + endTime ;
			}

			List<Map<String, Object>> results = this.jdbcTemplate.queryForList(sql);
			StringBuffer idsString = new StringBuffer();
			idsString.append("(");
			for (Map<String, Object> result: results) {
				idsString.append(result.get("userId")+",");
			}
			idsString = new StringBuffer(idsString.substring(0, idsString.length() - 1));
			idsString.append(")");
			RedisCilent.setString(FINANCIAL_APP_USER_COUNT+begTime+endTime,idsString.toString(),cacheService.getExpireSecond());
			ids = idsString.toString();
		}



		Integer count = 0;
		//判断是否有满足id，（1231，4124，124）
		if (ids.length() > 2) {

			String userSql = String.format("select count(*) from SysUser where id in %s and orgId in (select id from t_organization where code  like '%s' )", ids, orgCode + "-%");


			count = this.jdbcTemplate.queryForObject(userSql, Integer.class);
		}

		return count;
	}

	/**
	 * 部门理财师人数
	 */

	public  Integer getFinancialPlannerNumByOrgCode(String orgCode,
			Long begTime, Long endTime) {

		Integer size = 0;

		//String sql  = "SELECT count(*) FROM SysUser where  isInside = 1 and userOrgCode  like '" + orgCode + "-%'";

		String sql  = "SELECT count(*) FROM SysUser where  isInside = 1 and orgId in (select id from t_organization where code  like '" + orgCode + "-%' )";


		return getUserNum(begTime, endTime, sql);
	}

	/**
	 * 部门绑卡总人数, 不包括理财师
	 */

	public  Integer getTiedCardUserAccountByOrgCode(String orgCode,
			Long begTime, Long endTime) {

		//String sql = "SELECT count(1) FROM userview where tieCard = 1 and isInside = 0  and userOrgCode  like '" + orgCode + "-%'";


		String sql  = "SELECT count(*) FROM SysUser where tieCard = 1 and  isInside = 0 and orgId in (select id from t_organization where code  like '" + orgCode + "-%' )";


		return getUserNum(begTime, endTime, sql);

	}

	/**
	 * 部门客户总数
	 */

	public  Integer getRelationUserAccountByOrgCode(String orgCode,
			Long begTime, Long endTime) {

		//String sql = "SELECT count(1) FROM userview where isInside = 0 and userOrgCode  like '" + orgCode + "-%'";

		String sql  = "SELECT count(*) FROM SysUser where  isInside = 0 and orgId in (select id from t_organization where code  like '" + orgCode + "-%' )";


		return getUserNum(begTime, endTime, sql);
	}

	private Integer getUserNum(Long begTime, Long endTime, String sql) {
		if (begTime != null) {
			sql += " and registCts > " + begTime ;
		}

		if (endTime != null) {
			sql += " and  registCts < " + endTime ;
		}

		//logger.info("sql ={}", sql);

		Integer size = jdbcTemplate.queryForObject(sql, Integer.class);

		return size == null ? 0 : size;
	}

	/**
	 * 部门预约订单总数
	 */

	public  Integer getBespeakOrderNumByOrgCode(String orgCode, Long begTime,
			Long endTime, Long parId) {

		//String sql =  "SELECT count(1) FROM reservView01 where reservOrgCode like '" + orgCode + "-%'";

		String sql = "select count(1)  from t_reservation_info r left join t_product_info p on r.productId=p.id  where (p.isTest=0 or p.isTest is null) "
		+" and userId in (select id from SysUser where orgId in (select id from t_organization where code like '" + orgCode + "-%' ))";



		if (begTime != null) {
			sql += " and  cts > " + begTime;
		}

		if (endTime != null) {
			sql += " and  cts < " + endTime;
		}

		if (parId != null) {
			sql += " and productId in ( select id from t_product_info where parId = " + parId +") "  ;
		}

		//logger.info("部门预约订单总数 : getBespeakOrderNumByOrgCode sql = " + sql);
		Integer size = jdbcTemplate.queryForObject(sql, Integer.class);
		return size == null ? 0 : size;
	}

	/**
	 * 部门预约总金额
	 */

	public  BigDecimal getBespeakOrderAmountByOrgCode(String orgCode,
			Long begTime, Long endTime, Long parId) {

		//String sql = "SELECT sum(amount) FROM reservView01 where reservOrgCode like '" + orgCode + "-%'";

		String sql = "select sum(amount)  from t_reservation_info r left join t_product_info p on r.productId=p.id where (p.isTest=0 or p.isTest is null) and "
		+" userId in (select id from SysUser where orgId in (select id from t_organization where code like '" + orgCode + "-%' ))";


		if (begTime != null) {
			sql += " and  cts > " + begTime;
		}

		if (endTime != null) {
			sql += " and cts < " + endTime;
		}

		if (parId != null) {
			sql += " and productId in ( select id from t_product_info where parId = " + parId +") "  ;
		}

		BigDecimal totalAmount = jdbcTemplate.queryForObject(sql,
				BigDecimal.class);
		if (totalAmount == null) {
			totalAmount = new BigDecimal(0);
		}

		//logger.info("部门预约总金额 : getBespeakOrderAmountByOrgCode sql = " + sql);
		return totalAmount;

	}

	/**
	 * 部门付款订单总数
	 */

	public  Integer getPayOrderNumByOrgCode(String orgCode, Long begTime,
			Long endTime, Long parId) {
		//logger.info("部门付款订单总数: orgCode={}", orgCode);

		//String sql = "SELECT count(*) FROM niggerView where orderOrgCode like '"+ orgCode + "-%'";

		String sql = "SELECT count(*) FROM t_order_info r left join t_product_info p on r.productId=p.id  where (p.isTest=0 or p.isTest is null) and ( payStatus = 2 or payStatus = 4 ) and orgId in (select id from t_organization where code  like '" + orgCode + "-%' )";


		if (begTime != null) {
			sql += " and orderCreatedOn > " + begTime;
		}

		if (endTime != null) {
			sql += " and orderCreatedOn < " + endTime;
		}

		if (parId != null) {
			sql += " and productId in ( select id from t_product_info where parId = " + parId +") "  ;
		}
		//logger.info("部门付款订单总数: getPayOrderNumByOrgCode sql = " + sql);

		Integer size = jdbcTemplate.queryForObject(sql, Integer.class);

		//logger.info("部门付款订单总数: getPayOrderNumByOrgCode sql ={}, size={}",sql, size);
		return size == null ? 0 : size;

	}

	/**
	 * 部门付款订单金额
	 */

	public  BigDecimal getPayOrderAmountByOrgCode(String orgCode, Long begTime,
			Long endTime, Long parId) {

		//String sql = "SELECT sum(successAmount) FROM niggerView where orderOrgCode like '" + orgCode + "-%'";

		String sql = "SELECT sum(successAmount) FROM t_order_info  r left join t_product_info p on r.productId=p.id where "+
				"(p.isTest=0 or p.isTest is null) and ( payStatus = 2 or payStatus = 4 ) and  orgId in (select id from t_organization where code  like '" + orgCode + "-%' )";


		return getOrderAmount(begTime, endTime, parId, sql);
	}

	/**
	 * 部门付款订单折标金额
	 */
	public  BigDecimal getPayNiggerAmountByOrgCode(String orgCode, Long begTime,/*订单需要更改！*/
			Long endTime, Long parId) {

		//String sql = "SELECT sum(niggerAmount) FROM niggerView where orderOrgCode like '" + orgCode + "-%'";

		String sql = "SELECT sum(t.successAmount * p.niggerHead) FROM t_order_info t left join t_product_info p  on t.productId=p.id where (p.isTest=0 or p.isTest is null) and "
		+" ( t.payStatus = 2 or t.payStatus = 4 )  and  orgId in (select id from t_organization where code  like '" + orgCode + "-%' )";


		return getOrderAmount(begTime, endTime, parId, sql);
	}


	private BigDecimal getOrderAmount(Long begTime, Long endTime, Long parId, String sql) {
		if (begTime != null) {
			sql += " and orderCreatedOn > " + begTime;
		}

		if (endTime != null) {
			sql += " and orderCreatedOn < " + endTime;
		}

		if (parId != null) {
			sql += " and productId in ( select id from t_product_info where parId = " + parId +") "  ;
		}
		BigDecimal totalAmount = jdbcTemplate.queryForObject(sql,
				BigDecimal.class);

		if (totalAmount == null) {
			totalAmount = new BigDecimal(0);
		}

		//logger.info("getPayOrderAmountByOrgCode sql = " + sql);

		return totalAmount.setScale(2, BigDecimal.ROUND_HALF_UP);
	}

	public  StatisticsMsg getStatisticsMsg(int orgId, Long begTime, Long endTime, Long parId) {
		//logger.info("info in getAllInfoMsg id={}, startTime={}, endTime={}, parId={}", orgId, begTime, endTime, parId);

		StatisticsMsg statisInfo = new StatisticsMsg();
		Organization org = orgNewService.findById(orgId);
		if(org == null){
			//logger.info("org is null, id ={}", orgId);
		}
		statisInfo.setTopName(orgNewService.getChannelNameByOrgId(orgId));
		statisInfo.setSecondName(org.getFlag02());
		statisInfo.setShowName (org.getFlag02());

		statisInfo.setCode(org.getCode());
		statisInfo.setParentNodeId(org.getParentID());
		String code = org.getCode();
		// 该部门总人数
		statisInfo.setFinancialPlannerNum(getFinancialPlannerNumByOrgCode(code,
				begTime, endTime));

		// 使用 app 理财师人数
		statisInfo.setFinancialAppNum(this.getFinancialAppUserCountByOrgCode(code, begTime, endTime));

		// 总推荐人数
		statisInfo.setRelationUserAccount(getRelationUserAccountByOrgCode(code,
				begTime, endTime));

		// 绑卡用户总数
		statisInfo.setTiedCardUserAccount(getTiedCardUserAccountByOrgCode(code,
				begTime, endTime));

		// 预约订单总数
		statisInfo.setBespeakOrderNum(getBespeakOrderNumByOrgCode(code,
				begTime, endTime, parId));

		// 预约订单金额
		statisInfo.setBespeakOrderAmount(getBespeakOrderAmountByOrgCode(code,
				begTime, endTime, parId));

		// 付款订单总数
		statisInfo.setPayOrderNum(getPayOrderNumByOrgCode(code, begTime,
				endTime, parId));

		// 付款订单总金额
		statisInfo.setPayOrderAmount(getPayOrderAmountByOrgCode(code, begTime,
				endTime, parId));
		statisInfo.setNiggerAmmount(getPayNiggerAmountByOrgCode(code, begTime,
				endTime, parId));
		return statisInfo;
	}

	public  StatisticsMsg getVirsualDepStatisticsMsg(int orgId, Long begTime, Long endTime, Long parId) {
		//logger.info("getVirsualDepStatisticsMsg infor id={}, startTime={}, endTime={}, parId={}", orgId, begTime, endTime, parId);

		StatisticsMsg statisInfo = new StatisticsMsg();
		Organization org = orgNewService.findById(orgId);
		if (org == null) {
			//logger.info("org is null, id ={}", orgId);
		}
		statisInfo.setTopName(orgNewService.getChannelNameByOrgId(orgId));
		statisInfo.setSecondName(org.getFlag02() + "-直属");
		statisInfo.setShowName(org.getFlag02() + "-直属");
		statisInfo.setParentNodeId(orgId);

		List<String> subCodeList = orgNewService.getTotalVirsulDutCodes(orgId);
		if (subCodeList == null || subCodeList.size() == 0) {
			return null;
		}


		for (String code : subCodeList) {
			//logger.info("getVirsualDepStatisticsMsg code ={}", code);

			statisInfo.setFinancialPlannerNum(statisInfo.getFinancialPlannerNum() + getFinancialPlannerNumByOrgCode(code, begTime, endTime));

			//logger.info("getFinancialPlannerNumByOrgCode  ={}", getFinancialPlannerNumByOrgCode(code, begTime, endTime));
			// 总推荐人数
			statisInfo.setRelationUserAccount(statisInfo.getRelationUserAccount() + getRelationUserAccountByOrgCode(code,
					begTime, endTime));
			//logger.info("getRelationUserAccountByOrgCode  ={}", getRelationUserAccountByOrgCode(code, begTime, endTime));
			// 绑卡用户总数
			statisInfo.setTiedCardUserAccount(statisInfo.getTiedCardUserAccount() + getTiedCardUserAccountByOrgCode(code, begTime, endTime));

			//logger.info("getTiedCardUserAccountByOrgCode  ={}", getTiedCardUserAccountByOrgCode(code, begTime, endTime));
			// 预约订单总数
			statisInfo.setBespeakOrderNum(statisInfo.getBespeakOrderNum() + getBespeakOrderNumByOrgCode(code, begTime, endTime, parId));

			//logger.info("getBespeakOrderNumByOrgCode  ={}", getBespeakOrderNumByOrgCode(code, begTime, endTime, parId));
			// 预约订单总金额
			statisInfo.setBespeakOrderAmount(statisInfo.getBespeakOrderAmount().add(getBespeakOrderAmountByOrgCode(code, begTime, endTime, parId)));

			//logger.info("getBespeakOrderAmountByOrgCode  ={}", getBespeakOrderAmountByOrgCode(code, begTime, endTime, parId));
			// 付款订单总数
			statisInfo.setPayOrderNum(statisInfo.getPayOrderNum() + getPayOrderNumByOrgCode(code, begTime, endTime, parId));

			//logger.info("getPayOrderNumByOrgCode  ={}", getPayOrderNumByOrgCode(code, begTime, endTime, parId));
			// 付款订单总金额
			statisInfo.setPayOrderAmount(statisInfo.getPayOrderAmount().add(getPayOrderAmountByOrgCode(code, begTime, endTime, parId)));

			//logger.info("getPayOrderAmountByOrgCode  ={}", getPayOrderAmountByOrgCode(code, begTime, endTime, parId));

			statisInfo.setNiggerAmmount(statisInfo.getNiggerAmmount().add(getPayNiggerAmountByOrgCode(code, begTime, endTime, parId)));
			//logger.info("getPayNiggerAmountByOrgCode  ={}", getPayNiggerAmountByOrgCode(code, begTime, endTime, parId));

		}

		return statisInfo;
	}





}
